package com.rcp.host;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.rcp.objects.CustomerBean;
import com.rcp.objects.ReservationBean;

/**
 * Servlet implementation class Reservation
 */
@WebServlet("/Reservation")
public class Reservation extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	String className = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://cs3.calstatela.edu:3306/cs437group01";
	String dbusername = "cs437group01";
	String dbpassword = "VHj#dj!v";
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public Reservation() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		ResultSet resultSet = null;
		
		try{
			
			// Include the driver class
			Class.forName( className );
			
			// Establish a connection to the database
			Connection connection =
				DriverManager.getConnection(url, dbusername, dbpassword);			
			
			// Get a reference to the statement object that will execute our query
			// on the server
			Statement statement = connection.createStatement();
			
			if(request.getParameter("removeres") != null){
				
				statement.executeUpdate("DELETE FROM Reservations WHERE ReservationID = " + request.getParameter("removeres").toString().trim());
				
			}
			
			if(request.getParameter("findcustomer") != null){
				
				String keyword = request.getParameter("keyword");
				
				resultSet = statement.executeQuery("SELECT * FROM Customers WHERE FirstName LIKE '%" + keyword + "%' OR LastName LIKE '%" + keyword + "%' OR Address LIKE '%" + keyword + "%' OR PhoneNumber LIKE '%" + keyword + "%'");
			
				ArrayList<CustomerBean> customers = new ArrayList<CustomerBean>();
				
				while(resultSet.next()){
					
					customers.add(new CustomerBean(resultSet.getInt("CustomerID"), resultSet.getString("FirstName"), resultSet.getString("LastName"), resultSet.getString("Address"), resultSet.getString("PhoneNumber")));
					
				}
				
				request.setAttribute("searched", true);
				request.setAttribute("customerresult", customers);
				
				resultSet = null;
				
			}
			
			if(request.getParameter("createreservation") != null){
				
				String customerId = request.getParameter("createreservation").trim();
				
				resultSet = statement.executeQuery("SELECT * From Customers WHERE CustomerID = " + customerId);
				
				CustomerBean newReservationCustomer = null;
				
				while(resultSet.next()){
					
					newReservationCustomer = new CustomerBean(resultSet.getInt("CustomerID"), resultSet.getString("FirstName"), resultSet.getString("LastName"), resultSet.getString("Address"), resultSet.getString("PhoneNumber"));
					
				}
				
				request.setAttribute("reservenew", true);
				request.setAttribute("newcustomer", newReservationCustomer);
				
				resultSet = null;
				
			}
			
			if(request.getParameter("newreservation") != null){
				
				String date = request.getParameter("Date");
				String time = request.getParameter("Time");
				String id = request.getParameter("customerId");
				
				statement.executeUpdate("INSERT INTO Reservations (CustomerID, ReservationTime) VALUES ('" + id + "', STR_TO_DATE('"+ date + " " + time + "', '%m/%d/%Y %H:%i'))");
				
				request.setAttribute("ondatetime", date + " " + time);
				request.setAttribute("reservationsuccessful", true);
				
			}
			
			if(request.getParameter("finddate") != null){
				
				// Must be 02/17/1990
				String date = request.getParameter("date");
				
				
				ArrayList<ReservationBean> reservations = new ArrayList<ReservationBean>();
				
				resultSet = statement.executeQuery("SELECT R.ReservationID, R.CustomerID, R.ReservationTime, C.FirstName, C.LastName FROM Reservations R LEFT JOIN Customers C ON R.CustomerID = C.CustomerID  WHERE DATE_FORMAT(R.ReservationTime , '%m/%d/%Y') = '" + date + "'");
				
				while(resultSet.next()){
					
					reservations.add(new ReservationBean( resultSet.getInt("ReservationID"), resultSet.getInt("CustomerID"), resultSet.getString("ReservationTime"), resultSet.getString("FirstName") + " " + resultSet.getString("LastName")));
					
				}
				
				request.setAttribute("triedtofind", true);
				request.setAttribute("finddate", date);
				request.setAttribute("reservations", reservations);
				
				resultSet = null;
				
			}
			
			// Close our DB Connection
			connection.close();
									
		}
		catch(SQLException e){		
			e.printStackTrace();
		}
		catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		
		String pathToView = "/WEB-INF/host/Reservation.jsp";
		
		RequestDispatcher dispatcher =
				request.getRequestDispatcher( pathToView );
			
			dispatcher.forward(request, response);
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}
